/*
 	
*/
ALTER PROC SP_APPROVE_CREDIT_NOTE_LINEITEMS @CN_ID 	INT, 
					@LINEITEM_ID 	INT,
					@MKTG_HOLD_REASON_CODE VARCHAR(4),
					@FIN_HOLD_REASON_CODE VARCHAR(4),
					@OPS_HOLD_REASON_CODE VARCHAR(4),
					@SALES_HOLD_REASON_CODE VARCHAR(4),
					@USER_ID VARCHAR(50)
AS
BEGIN
	DECLARE @CNLT_ID INT
	DECLARE @DB_MKTG_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @DB_FIN_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @DB_OPS_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @DB_SALES_HOLD_REASON_CODE VARCHAR(4)

	SELECT @CNLT_ID = CNLT_ID FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND LINEITEM_ID = @LINEITEM_ID
	
	IF ( @CNLT_ID > 0 )
	 BEGIN
		--SELECT @DB_MKTG_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'MARKETING' AND CNLT_ID = @CNLT_ID
		SELECT @DB_FIN_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'FINANCE' AND CNLT_ID = @CNLT_ID
		/*SELECT @DB_OPS_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'OPERATIONS' AND CNLT_ID = @CNLT_ID
		SELECT @DB_SALES_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'SALES' AND CNLT_ID = @CNLT_ID*/

		/*IF ( @DB_MKTG_HOLD_REASON_CODE <> @MKTG_HOLD_REASON_CODE )
		 BEGIN
			UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = @MKTG_HOLD_REASON_CODE, HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE HOLD_BY_GROUP = 'MARKETING' AND CNLT_ID = @CNLT_ID
		 END*/

		IF ( @DB_FIN_HOLD_REASON_CODE <> @FIN_HOLD_REASON_CODE )
		 BEGIN
			UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = @FIN_HOLD_REASON_CODE, HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE HOLD_BY_GROUP = 'FINANCE' AND CNLT_ID = @CNLT_ID
		 END

		/*IF ( @DB_OPS_HOLD_REASON_CODE <> @OPS_HOLD_REASON_CODE )
		 BEGIN
			UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = @OPS_HOLD_REASON_CODE, HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE HOLD_BY_GROUP = 'OPERATIONS' AND CNLT_ID = @CNLT_ID
		 END

		IF ( @DB_SALES_HOLD_REASON_CODE <> @SALES_HOLD_REASON_CODE )
		 BEGIN
			UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = @SALES_HOLD_REASON_CODE, HOLD_BY_USER = @USER_ID, [TIMESTAMP] = CURRENT_TIMESTAMP WHERE HOLD_BY_GROUP = 'SALES' AND CNLT_ID = @CNLT_ID
		 END*/

		--MAKING HOLDS TO APPROVED OTHER THAN FINANCE HOLD AS WE HAVE ONLY FINANCE HOLD FOR CREDIT/DEBIT NOTE
		UPDATE CREDIT_NOTE_HOLDS_TABLE SET HOLD_REASON_CODE = '', HOLD_BY_USER = 'SYSTEM', [TIMESTAMP] = CURRENT_TIMESTAMP WHERE HOLD_BY_GROUP != 'FINANCE' AND CNLT_ID = @CNLT_ID 

		--CHECKING LINE ITEM HOLDS ARE APPROVED OR NOT
		IF ( (SELECT COUNT(*) FROM CREDIT_NOTE_HOLDS_TABLE WHERE CNLT_ID = @CNLT_ID AND HOLD_REASON_CODE != '') = 0 )
		 BEGIN
			UPDATE CREDIT_NOTE_LINE_ITEM_TABLE SET STATUS = 'APPROVED' WHERE CNLT_ID = @CNLT_ID
		 END

		DECLARE @TOTAL_LINEITES INT
		DECLARE @CANCELLED_LINEITEMS_COUNT INT
		DECLARE @APPROVED_LINEITEMS_COUNT INT
		DECLARE @HOLD_LINEITEMS_COUNT INT	
	
		SELECT @TOTAL_LINEITES = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID			
		SELECT @CANCELLED_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'CANCELLED'
		SELECT @APPROVED_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'APPROVED'
		SELECT @HOLD_LINEITEMS_COUNT = COUNT(*) FROM CREDIT_NOTE_LINE_ITEM_TABLE WHERE CN_ID = @CN_ID AND STATUS = 'HOLD'
		
		IF ( ISNULL(@CANCELLED_LINEITEMS_COUNT,0) = @TOTAL_LINEITES ) --CREDIT NOTE IS CANCELLED
		 BEGIN
			UPDATE CREDIT_NOTE_TABLE SET STATUS = 'CANCELLED' WHERE CREDITNOTE_ID = @CN_ID
		 END
		ELSE IF ( (ISNULL(@CANCELLED_LINEITEMS_COUNT,0)+ISNULL(@APPROVED_LINEITEMS_COUNT,0)) = @TOTAL_LINEITES ) --CREDIT NOTE IS APPROVED
		 BEGIN
			UPDATE CREDIT_NOTE_TABLE SET STATUS = 'APPROVED' WHERE CREDITNOTE_ID = @CN_ID
		 END
		ELSE IF ( ISNULL(@HOLD_LINEITEMS_COUNT,0) > 0 ) --CREDIT NOTE IS IN HOLD
		 BEGIN
			UPDATE CREDIT_NOTE_TABLE SET STATUS = 'HOLD' WHERE CREDITNOTE_ID = @CN_ID
		 END
	 END

	--DUMMY UPDATE
	UPDATE CREDIT_NOTE_TABLE SET STATUS = STATUS WHERE CREDITNOTE_ID = -1
END
					
